package view;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TopComplaintMonthDAO {

	private DBConnect con = null;
	private long lnoexist;

	public TopComplaintMonthDAO(DBConnect incon) {
		con = incon;
		try {
			con.getConnection().setAutoCommit(false);
		} catch (SQLException e) {
		}
	}

	public String ViewComplaintMonth() {
		StringBuffer topComplaintMonth = new StringBuffer(1000);
		PreparedStatement ps = null;
		ResultSet rs = null;
		String Month = "";
		String Count = "";

		try {
			ps = con.getConnection()
					.prepareStatement(
							"WITH COMPLAINT_MONTHS AS "
									+ "(SELECT PERSON_ID, FIRST_NAME, LAST_NAME, "
									+ "TO_CHAR(COMPLAINT_DATE, 'MONTH YYYY') AS MONTHS "
									+ "FROM "+con.getSchema()+".FACTORY_ORDER FO INNER JOIN "
									+ ""+con.getSchema()+".PERSON P ON FO.CUSTOMER_ID = P.PERSON_ID "
									+ "INNER JOIN "+con.getSchema()+".CUST_COMPLAINT CC "
									+ "ON FO.ORDER_ID = CC.ORDER_ID),"
									+ "MONTH_COMPLAINT_COUNT AS "
									+ "(SELECT MONTHS, COUNT(MONTHS) AS COMPLAINT_COUNT "
									+ "FROM COMPLAINT_MONTHS "
									+ "GROUP BY MONTHS "
									+ "ORDER BY COMPLAINT_COUNT DESC) "
									+ "SELECT MONTHS, COMPLAINT_COUNT "
									+ "FROM MONTH_COMPLAINT_COUNT "
									+ "WHERE ROWNUM = 1");
			rs = ps.executeQuery();

			while (rs.next()) {
				Month = rs.getString(1);
				Count = rs.getString(2);
			}
			topComplaintMonth.append("Month: " + Month + "\nComplaint Count: "
					+ Count);
			rs.close();
			ps.close();
		} catch (SQLException e) {
			topComplaintMonth.append("SQL Error in Top Complaint Month"
					+ e.getSQLState() + "\nError Code: " + e.getErrorCode()
					+ "\nMessage: " + e.getMessage());
			System.exit(1);
		} catch (Exception e) {
			topComplaintMonth
					.append("unknown Error in Top Complaint Month \nMessage: "
							+ e.getMessage());
			System.exit(1);
		}
		return topComplaintMonth.toString();
	}
}